import pandas as pd
import numpy as np
from pulp import LpProblem, LpMinimize, LpVariable, lpSum, LpStatus, value
from datetime import datetime
import os


# 1. 数据输入与预处理
def load_data(file_path="附件1.xlsx"):
    """
    从Excel文件中读取数据，假设包含门店、商品、需求和初始库存
    返回DataFrame，包含'store' (门店), 'product' (商品), 'demand' (需求), 'initial_inventory' (初始库存)
    """
    try:
        # 尝试读取Excel文件
        # 假设Excel文件结构：列名分别为 '公司名称' (store), '货号' (product), '顾客需求' (demand), '初始库存' (initial_inventory)
        df = pd.read_excel(file_path, sheet_name=0, usecols=['公司名称', '货号', '顾客需求', '初始库存'])
        df.columns = ['store', 'product', 'demand', 'initial_inventory']

        # 数据清理
        # 1. 填充缺失值为0
        df = df.fillna(0)
        # 2. 转换数据类型为数值型
        df['demand'] = pd.to_numeric(df['demand'], errors='coerce').fillna(0)
        df['initial_inventory'] = pd.to_numeric(df['initial_inventory'], errors='coerce').fillna(0)
        # 3. 确保所有值非负
        df['demand'] = df['demand'].clip(lower=0)
        df['initial_inventory'] = df['initial_inventory'].clip(lower=0)
        # 4. 去除重复行
        df = df.drop_duplicates(subset=['store', 'product'], keep='first')

        return df
    except Exception as e:
        print(f"读取文件时出错: {e}")
        print("将使用示例数据继续运行")
        return create_sample_data()


def create_sample_data():
    """创建示例数据"""
    stores = ['s1', 's2', 's3']
    products = ['p1', 'p2', 'p3']

    sample_data = []
    for store in stores:
        for product in products:
            demand = np.random.randint(100, 500)
            initial_inventory = np.random.randint(20, 100)
            sample_data.append({
                'store': store,
                'product': product,
                'demand': demand,
                'initial_inventory': initial_inventory
            })

    return pd.DataFrame(sample_data)


# 2. 鲁棒性分析模型
def analyze_robustness(df, warehouses=['w1', 'w2'], vehicle_capacity=500, time_window=(0, 24), sigma_factor=0.1):
    """
    构建并求解鲁棒性分析模型，考虑预测偏差
    输入：DataFrame (门店、商品、需求、初始库存), 仓库列表, 车辆容量, 时间窗口, 偏差标准差因子
    输出：最优运输计划、总成本和鲁棒性评估
    """
    # 提取参数
    stores = df['store'].unique().tolist()
    products = df['product'].unique().tolist()

    # 创建需求字典和初始库存字典
    demand_dict = {}
    initial_inv_dict = {}
    for _, row in df.iterrows():
        key = (row['store'], row['product'])
        demand_dict[key] = row['demand']
        initial_inv_dict[key] = row['initial_inventory']

    print(f"门店数量: {len(stores)}, 商品种类: {len(products)}, 仓库数量: {len(warehouses)}")

    # 假设参数 - 动态生成以避免KeyError
    # 1. 运输成本
    transport_cost = {}
    for w in warehouses:
        for s in stores:
            # 随机生成运输成本，距离越远成本越高
            base_cost = 0.5 + np.random.rand() * 2
            transport_cost[(w, s)] = base_cost

    # 2. 库存成本
    inventory_cost = {}
    for s in stores:
        inventory_cost[s] = {}
        for p in products:
            # 随机生成库存成本，贵重商品成本高
            inventory_cost[s][p] = 0.1 + np.random.rand() * 0.3

    # 3. 时间窗口
    T_start, T_end = time_window

    # 4. 预测偏差 - 正态分布随机生成
    np.random.seed(42)  # 固定随机种子以复现结果
    epsilon = {}
    for s in stores:
        for p in products:
            key = (s, p)
            base_demand = demand_dict.get(key, 0)
            # 避免需求为0时出现NaN
            std_dev = sigma_factor * base_demand if base_demand > 0 else 0
            epsilon[key] = np.random.normal(0, std_dev)

    print(f"预测偏差示例: {dict(list(epsilon.items())[:5])}")  # 显示前5个偏差示例

    # 创建问题
    prob = LpProblem("Robustness_Analysis", LpMinimize)

    # 定义变量
    # 1. 运输变量
    x = LpVariable.dicts("运输量",
                         [(w, s, p) for w in warehouses for s in stores for p in products],
                         lowBound=0, cat='Continuous')

    # 2. 库存变量
    I = LpVariable.dicts("期末库存",
                         [(s, p) for s in stores for p in products],
                         lowBound=0, cat='Continuous')

    # 目标函数：最小化运输成本 + 库存成本
    transport_cost_component = lpSum(
        transport_cost[(w, s)] * x[(w, s, p)]
        for w in warehouses for s in stores for p in products
    )

    inventory_cost_component = lpSum(
        inventory_cost[s][p] * I[(s, p)]
        for s in stores for p in products
    )

    prob += transport_cost_component + inventory_cost_component

    # 约束条件
    # 1. 需求满足（考虑偏差）：期末库存 = 初始库存 + 运入库存 - (需求 + 偏差)
    for s in stores:
        for p in products:
            key = (s, p)
            base_demand = demand_dict.get(key, 0)
            demand_with_error = base_demand + epsilon.get(key, 0)
            initial_inv = initial_inv_dict.get(key, 0)

            prob += I[key] == initial_inv + lpSum(x[(w, s, p)] for w in warehouses) - demand_with_error
            prob += I[key] >= 0  # 不允许缺货

    # 2. 库存容量限制（假设最大库存为需求的两倍）
    for s in stores:
        for p in products:
            key = (s, p)
            max_inv = 2 * demand_dict.get(key, 0)
            if max_inv > 0:
                prob += I[key] <= max_inv

    # 3. 运输能力限制
    for w in warehouses:
        prob += lpSum(x[(w, s, p)] for s in stores for p in products) <= vehicle_capacity

    # 4. 运输量非负
    for w in warehouses:
        for s in stores:
            for p in products:
                prob += x[(w, s, p)] >= 0

    # 求解
    print("开始求解优化问题...")
    start_time = datetime.now()
    prob.solve()
    end_time = datetime.now()
    print(f"求解完成，耗时: {(end_time - start_time).total_seconds():.2f}秒")

    # 输出结果
    print(f"\n优化状态: {LpStatus[prob.status]}")
    if LpStatus[prob.status] != "Optimal":
        print("警告: 未找到最优解，结果可能不是最优的")

    # 计算总成本
    total_transport_cost = value(transport_cost_component)
    total_inventory_cost = value(inventory_cost_component)
    total_cost = total_transport_cost + total_inventory_cost

    print(f"\n总成本: {total_cost:.2f} 单位货币")
    print(f"运输成本: {total_transport_cost:.2f} ({total_transport_cost / total_cost * 100:.1f}%)")
    print(f"库存成本: {total_inventory_cost:.2f} ({total_inventory_cost / total_cost * 100:.1f}%)")

    # 收集最优运输计划
    optimal_plan = []
    for w in warehouses:
        for s in stores:
            for p in products:
                var_value = value(x[(w, s, p)])
                if var_value > 0.01:  # 忽略非常小的值
                    cost = transport_cost[(w, s)] * var_value
                    optimal_plan.append({
                        'warehouse': w,
                        'store': s,
                        'product': p,
                        'quantity': round(var_value, 2),
                        'cost': round(cost, 2)
                    })

    # 收集库存信息
    inventory_info = []
    for s in stores:
        for p in products:
            key = (s, p)
            initial = initial_inv_dict.get(key, 0)
            final = value(I[key])
            demand_val = demand_dict.get(key, 0)
            inventory_info.append({
                'store': s,
                'product': p,
                'initial_inventory': initial,
                'final_inventory': round(final, 2),
                'demand': demand_val,
                'demand_with_error': demand_val + epsilon.get(key, 0)
            })

    # 敏感性分析（模拟不同偏差水平）
    sensitivity_results = {}
    print("\n开始敏感性分析...")
    for deviation_factor in [0.0, 0.1, 0.2]:  # 偏差因子 0%、10%、20%
        print(f"分析偏差因子: {deviation_factor * 100}%...")

        # 生成新的预测偏差
        epsilon_adjusted = {}
        for s in stores:
            for p in products:
                key = (s, p)
                base_demand = demand_dict.get(key, 0)
                std_dev = deviation_factor * base_demand if base_demand > 0 else 0
                epsilon_adjusted[key] = np.random.normal(0, std_dev)

        # 创建新问题
        prob_adjusted = LpProblem("Sensitivity_Analysis", LpMinimize)

        # 定义变量
        x_adjusted = LpVariable.dicts("x", [(w, s, p) for w in warehouses for s in stores for p in products],
                                      lowBound=0, cat='Continuous')
        I_adjusted = LpVariable.dicts("I", [(s, p) for s in stores for p in products],
                                      lowBound=0, cat='Continuous')

        # 目标函数
        prob_adjusted += lpSum(
            transport_cost[(w, s)] * x_adjusted[(w, s, p)]
            for w in warehouses for s in stores for p in products
        ) + lpSum(
            inventory_cost[s][p] * I_adjusted[(s, p)]
            for s in stores for p in products
        )

        # 约束条件
        for s in stores:
            for p in products:
                key = (s, p)
                base_demand = demand_dict.get(key, 0)
                demand_with_error = base_demand + epsilon_adjusted.get(key, 0)
                initial_inv = initial_inv_dict.get(key, 0)

                prob_adjusted += I_adjusted[key] == initial_inv + lpSum(
                    x_adjusted[(w, s, p)] for w in warehouses) - demand_with_error
                prob_adjusted += I_adjusted[key] >= 0

                max_inv = 2 * demand_dict.get(key, 0)
                if max_inv > 0:
                    prob_adjusted += I_adjusted[key] <= max_inv

        for w in warehouses:
            prob_adjusted += lpSum(x_adjusted[(w, s, p)] for s in stores for p in products) <= vehicle_capacity

        # 求解
        prob_adjusted.solve()
        sensitivity_results[deviation_factor] = value(prob_adjusted.objective.value())

    print("\n敏感性分析结果 (总成本随偏差变化):")
    for factor, cost in sensitivity_results.items():
        print(f"偏差因子 {factor * 100}%: 总成本 {cost:.2f} 单位货币")

    return optimal_plan, total_cost, sensitivity_results, inventory_info


# 3. 结果保存
def save_results(optimal_plan, total_cost, sensitivity_results, inventory_info,
                 output_file="robustness_analysis_results.xlsx"):
    """保存优化结果到Excel文件"""
    # 创建运输计划DataFrame
    transport_df = pd.DataFrame(optimal_plan)

    # 创建库存信息DataFrame
    inventory_df = pd.DataFrame(inventory_info)

    # 创建敏感性分析DataFrame
    sensitivity_df = pd.DataFrame({
        'deviation_factor': [f"{k * 100}%" for k in sensitivity_results.keys()],
        'total_cost': sensitivity_results.values()
    })

    # 创建汇总信息
    summary = {
        'total_cost': [total_cost],
        'total_transport_quantity': [transport_df['quantity'].sum()],
        'average_transport_cost': [
            transport_df['cost'].sum() / transport_df['quantity'].sum() if transport_df['quantity'].sum() > 0 else 0]
    }
    summary_df = pd.DataFrame(summary)

    # 保存到Excel
    with pd.ExcelWriter(output_file) as writer:
        transport_df.to_excel(writer, sheet_name='运输计划', index=False)
        inventory_df.to_excel(writer, sheet_name='库存信息', index=False)
        sensitivity_df.to_excel(writer, sheet_name='敏感性分析', index=False)
        summary_df.to_excel(writer, sheet_name='汇总', index=False)

    print(f"\n结果已保存到: {output_file}")


# 4. 主程序
def main():
    print("=" * 50)
    print("鲁棒性物流调度分析系统")
    print("=" * 50)
    print("程序开始运行...")

    # 加载数据
    file_path = "附件1.xlsx"  # 请替换为实际文件路径
    print(f"\n加载数据文件: {file_path}")
    data = load_data(file_path)

    # 打印输入数据统计信息
    print("\n数据统计:")
    print(f"记录数: {len(data)}")
    print(f"门店数量: {data['store'].nunique()}")
    print(f"商品种类: {data['product'].nunique()}")
    print(f"总需求: {data['demand'].sum():.2f}")
    print(f"总初始库存: {data['initial_inventory'].sum():.2f}")

    # 运行鲁棒性分析模型
    warehouses = ['w1', 'w2', 'w3']  # 仓库列表
    vehicle_capacity = 800  # 车辆容量
    time_window = (0, 24)  # 时间窗口（0到24小时）
    sigma_factor = 0.1  # 偏差标准差因子（10%）

    print("\n优化参数:")
    print(f"仓库: {warehouses}")
    print(f"车辆容量: {vehicle_capacity}")
    print(f"时间窗口: {time_window[0]}-{time_window[1]}小时")
    print(f"偏差标准差因子: {sigma_factor * 100}%")

    optimal_plan, total_cost, sensitivity_results, inventory_info = analyze_robustness(
        data,
        warehouses=warehouses,
        vehicle_capacity=vehicle_capacity,
        time_window=time_window,
        sigma_factor=sigma_factor
    )

    # 保存结果
    save_results(optimal_plan, total_cost, sensitivity_results, inventory_info)

    # 当前时间戳
    current_time = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    print(f"\n程序运行完成，时间：{current_time}")


if __name__ == "__main__":
    main()